package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.LogEntity;
import br.com.skytef.entity.RelatorioTecnicoEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

	public class RelTecDao {
	
		public Connection connection;

		@SuppressWarnings("static-access")
		public RelTecDao(){
			this.connection = new ConnectionFactory().getConnection();
		}

		public RelTecDao(HttpSession session){
			this.connection = utils.recoverSessionDBConnection(session);
		}

		public RelTecDao(Connection con){
			this.connection = con;
		}

		public Connection getConnection(){
			return connection;
		}

		/*--------------------------------------------------------------------------------------------------------
		 * Insert
		 --------------------------------------------------------------------------------------------------------*/
		public RelatorioTecnicoEntity insertTec (RelatorioTecnicoEntity tecEntity) throws Exception {

			try {

				PreparedStatement stmt = connection.prepareStatement(this.getQryInsertTec());
				stmt.setDate(1,tecEntity.getData());
				stmt.setString(2,tecEntity.getTipoConsulta());
				stmt.setTime(3,tecEntity.getHoraInicioOperacao());
				stmt.setTime(4,tecEntity.getPosHora());
				stmt.setTime(5,tecEntity.getHoraRequisicao());
				stmt.setTime(6,tecEntity.getHoraRetorno());
				stmt.setString(7,tecEntity.getAlgGrupo());
				stmt.setString(8,tecEntity.getAlgLoja());
				stmt.setString(9,tecEntity.getAlgUrl());
				stmt.setString(10,tecEntity.getAlgCliente());
				stmt.setString(11,tecEntity.getAlgTransCodigo());
				stmt.setString(12,tecEntity.getAlgTransMsg());
				stmt.setString(13,tecEntity.getPosSerial());
				stmt.setString(14,tecEntity.getPosModelo());
				stmt.setString(15,tecEntity.getPosNavsVersao());
				stmt.setString(16,tecEntity.getPosOsVersao());
				stmt.setString(17,tecEntity.getPosLuaVersao());

				stmt.execute();
				stmt.close();

				PreparedStatement stmt2 = connection.prepareStatement("SELECT Last_insert_id() as id FROM log;");
				ResultSet rs = stmt2.executeQuery();

				int id = 0;
				if (rs.next()) {
					id = rs.getInt("id");
					tecEntity.setIdLog(id);
				}
				stmt2.execute();
				stmt2.close();
			} catch (SQLException e) {
				throw new Exception(e);
			} catch (Exception e) {
				throw new Exception(e);
			}
			return tecEntity;
		}

		/*--------------------------------------------------------------------------------------------------------
		 * update
		 --------------------------------------------------------------------------------------------------------*/
		public void updateTec (RelatorioTecnicoEntity tecEntity) throws Exception {

			try {

				PreparedStatement stmt = connection.prepareStatement(this.getQryUpdateTec());
				stmt.setDate(1,tecEntity.getData());
				stmt.setString(2,tecEntity.getTipoConsulta());
				stmt.setTime(3,tecEntity.getHoraInicioOperacao());
				stmt.setTime(4,tecEntity.getPosHora());
				stmt.setTime(5,tecEntity.getHoraRequisicao());
				stmt.setTime(6,tecEntity.getHoraRetorno());
				stmt.setString(7,tecEntity.getAlgGrupo());
				stmt.setString(8,tecEntity.getAlgLoja());
				stmt.setString(9,tecEntity.getAlgUrl());
				stmt.setString(10,tecEntity.getAlgCliente());
				stmt.setString(11,tecEntity.getAlgTransCodigo());
				stmt.setString(12,tecEntity.getAlgTransMsg());
				stmt.setString(13,tecEntity.getPosSerial());
				stmt.setString(14,tecEntity.getPosModelo());
				stmt.setString(15,tecEntity.getPosNavsVersao());
				stmt.setString(16,tecEntity.getPosOsVersao());
				stmt.setString(16,tecEntity.getPosOsVersao());
				stmt.setString(17, tecEntity.getPosLuaVersao());
				stmt.setInt(18,tecEntity.getIdLog());			

				stmt.execute();
				stmt.close();

			} catch (SQLException e) {
				throw new Exception(e);
			} catch (Exception e) {
				throw new Exception(e);
			}
		}

		/*--------------------------------------------------------------------------------------------------------
		 * Select
		 --------------------------------------------------------------------------------------------------------*/

		public  List<RelatorioTecnicoEntity> searchAll() throws SQLException{
			Connection connection = new ConnectionFactory().getConnection();
			try {
				List<RelatorioTecnicoEntity> tecList = new ArrayList<RelatorioTecnicoEntity>();

				PreparedStatement stmt = connection.prepareStatement(this.getQrySearchAll());
				ResultSet rs = stmt.executeQuery();
				while (rs.next()) {
					RelatorioTecnicoEntity tec = new RelatorioTecnicoEntity();

					tec.setIdLog(rs.getInt("idlog"));
					tec.setData(rs.getDate("data"));
					tec.setTipoConsulta(rs.getString("tipo_cons"));
					tec.setPosHora(rs.getTime("hora_pos"));
					tec.setHoraInicioOperacao(rs.getTime("hora_inicio_operacao"));
					tec.setHoraRequisicao(rs.getTime("hora_requisicao"));
					tec.setHoraRetorno(rs.getTime("hora_retorno"));
					tec.setAlgGrupo(rs.getString("alg_grupo"));
					tec.setAlgLoja(rs.getString("alg_loja"));
					tec.setAlgUrl(rs.getString("alg_url"));
					tec.setAlgCliente(rs.getString("alg_cliente"));
					tec.setAlgTransCodigo(rs.getString("alg_trans_codigo"));
					tec.setAlgTransMsg(rs.getString("alg_trans_msg"));
					tec.setPosSerial(rs.getString("serial_pos"));
					tec.setPosModelo(rs.getString("modelo_pos"));
					tec.setPosOsVersao(rs.getString("os_ver_pos"));
					tec.setPosNavsVersao(rs.getString("navs_ver_pos"));
					tec.setPosLuaVersao(rs.getString("lua_ver_pos"));	

					tecList.add(tec);
				} 

				rs.close();
				stmt.close();
				connection.close();
				return tecList;

			} catch (SQLException e) {
				connection.rollback();
				connection.close();
				throw new RuntimeException(e);
			}

		}
		
		
		public  List<RelatorioTecnicoEntity> searchAllByDate(String ini, String fim) throws SQLException{
			Connection connection = new ConnectionFactory().getConnection();
			try {
				List<RelatorioTecnicoEntity> tecList = new ArrayList<RelatorioTecnicoEntity>();

				PreparedStatement stmt = connection.prepareStatement(this.getQrySearchAllByDate(ini, fim));
				ResultSet rs = stmt.executeQuery();
				while (rs.next()) {
					RelatorioTecnicoEntity tec = new RelatorioTecnicoEntity();

					tec.setIdLog(rs.getInt("idlog"));
					tec.setData(rs.getDate("data"));
					tec.setTipoConsulta(rs.getString("tipo_cons"));
					tec.setPosHora(rs.getTime("hora_pos"));
					tec.setHoraInicioOperacao(rs.getTime("hora_inicio_operacao"));
					tec.setHoraRequisicao(rs.getTime("hora_requisicao"));
					tec.setHoraRetorno(rs.getTime("hora_retorno"));
					tec.setAlgGrupo(rs.getString("alg_grupo"));
					tec.setAlgLoja(rs.getString("alg_loja"));
					tec.setAlgUrl(rs.getString("alg_url"));
					tec.setAlgCliente(rs.getString("alg_cliente"));
					tec.setAlgTransCodigo(rs.getString("alg_trans_codigo"));
					tec.setAlgTransMsg(rs.getString("alg_trans_msg"));
					tec.setPosSerial(rs.getString("serial_pos"));
					tec.setPosModelo(rs.getString("modelo_pos"));
					tec.setPosOsVersao(rs.getString("os_ver_pos"));
					tec.setPosNavsVersao(rs.getString("navs_ver_pos"));
					tec.setPosLuaVersao(rs.getString("lua_ver_pos"));	

					tecList.add(tec);
				} 

				rs.close();
				stmt.close();
				connection.close();
				return tecList;

			} catch (SQLException e) {
				connection.rollback();
				connection.close();
				throw new RuntimeException(e);
			}

		}



		/*--------------------------------------------------------------------------------------------------------
		 * Query
		 --------------------------------------------------------------------------------------------------------*/
		private String getQryInsertTec() {

			StringBuffer sql = new StringBuffer();

			sql.append("INSERT INTO log ");
			sql.append("            (data, ");
			sql.append("             tipo_cons, ");
			sql.append("             hora_inicio_operacao, ");
			sql.append("             hora_pos, ");
			sql.append("             hora_requisicao, ");
			sql.append("             hora_retorno, ");
			sql.append("             alg_grupo, ");
			sql.append("             alg_loja, ");
			sql.append("             alg_url, ");
			sql.append("             alg_cliente, ");
			sql.append("             alg_trans_codigo, ");
			sql.append("             alg_trans_msg, ");
			sql.append("             serial_pos, ");
			sql.append("             modelo_pos, ");
			sql.append("             navs_ver_pos, ");
			sql.append("             os_ver_pos, ");
			sql.append("			 lua_ver_pos)");
			sql.append("VALUES      (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ");

			return sql.toString();

		}

		private String getQryUpdateTec() {

			StringBuffer sql = new StringBuffer();	

			sql.append("UPDATE log ");
			sql.append("SET    data = ?, ");
			sql.append("       tipo_cons = ?, ");
			sql.append("       hora_inicio_operacao = ?, ");
			sql.append("       hora_pos = ?, ");
			sql.append("       hora_requisicao = ?, ");
			sql.append("       hora_retorno = ?, ");
			sql.append("       alg_grupo = ?, ");
			sql.append("       alg_loja = ?, ");
			sql.append("       alg_url = ?, ");
			sql.append("       alg_cliente = ?, ");
			sql.append("       alg_trans_codigo = ?, ");
			sql.append("       alg_trans_msg = ?, ");
			sql.append("       serial_pos = ?, ");
			sql.append("       modelo_pos = ?, ");
			sql.append("       navs_ver_pos = ?, ");
			sql.append("       os_ver_pos = ? ");
			sql.append("	   lua_ver_pos = ? ");	
			sql.append("WHERE  idlog = ?; ");  

			return sql.toString();
		}

		private String getQrySelectTec() {

			StringBuffer sql = new StringBuffer();			
			sql.append("SELECT * FROM log WHERE  idlog = ?; ");

			return sql.toString();
		}

		private String getQrySearchAll() {
			StringBuffer sql = new StringBuffer();	
			sql.append("SELECT * FROM log ");
			sql.append("ORDER BY data DESC, hora_pos ASC ;");

			return sql.toString();
		}
		
		
		private String getQrySearchAllByDate(String dtIni, String dtFim) {
			StringBuffer sql = new StringBuffer();	
			sql.append("SELECT * FROM log WHERE data between '" + dtIni + "' and '" +dtFim + "' ORDER BY data DESC, hora_pos ASC ;" );

			return sql.toString();
		}
	}

